Duplicate Transaction Fix [Remittance]

Avoid App duplicate API call on Remittance Transaction Submit

This approach might change based on the Area.

1. Change the SP to not to insert the duplicate call from the app.

Added Lock in sender table to get the lock on Sender level.

Added some filter on the to check if there is any duplicate transaction. If yes throwing the error from SP as you can see below.

ALTER PROCEDURE [dbo].[TransactionInsert_V4]
     @transactionId uniqueidentifier
    ,@created datetime = NULL
    ,@senderId uniqueidentifier
    ,@beneficiaryId uniqueidentifier        
    ,@minorityExchangeRate DECIMAL(18,10)
    ,@minorityFee DECIMAL(18,2)
    ,@totalTransactionAmount DECIMAL(18,2)
    ,@sentAmount DECIMAL(18,2)
    ,@beneficiaryReceivableAmount DECIMAL(18,2)
    ,@senderCurrency NVARCHAR(3)
    ,@beneficiaryCurrency NVARCHAR(3)   
    ,@status INT
    ,@usedExchangeRateId uniqueidentifier NULL = NULL       
    ,@minorityRefId  nvarchar(50) = NULL
    ,@clientIp nvarchar(50) = NULL  
    ,@endProviderId INT NULL
    ,@licenseId INT NULL
    ,@transactionType INT NULL
    ,@endProviderReferenceId nvarchar(100)
    ,@expectedDeliveryOn datetime null
    ,@expirationDate datetime null
    ,@externalReferenceId uniqueidentifier null
    ,@hasCancellationAttempted bit null
    ,@lastStatusTime datetime null
    ,@statusReason nvarchar(200) null
    ,@transactionFinalizedReferenceId uniqueidentifier null
    ,@transactionPreAuthReferenceId uniqueidentifier null   
    ,@updated datetime null
    ,@retryingTransactionId uniqueidentifier null
    ,@isRetryable bit null
    ,@isReRouted bit null = NULL
    ,@reRoutedReason int null = NULL
    ,@minorityPromoExchangeRate DECIMAL(18,10)
    ,@promoId uniqueidentifier null
    ,@promoFeeId uniqueidentifier null = null
    ,@promoFee DECIMAL(18,2) null = null
AS
BEGIN

BEGIN TRANSACTION;
    ---TO Avoid the Duplicate Submit from the app.
    SELECT SenderId FROM Sender WITH(UPDLOCK, SERIALIZABLE) WHERE SenderId = @senderId

    INSERT INTO [Transaction]
        ([TransactionId]        
        ,[SenderId]
        ,[BeneficiaryId]
        ,[BeneficiaryAccountId]     
        ,[MinorityExchangeRate]
        ,[MinorityFee]
        ,[TotalTransactionAmount]
        ,[SentAmount]
        ,[BeneficiaryReceivableAmount]
        ,[SenderCurrency]
        ,[BeneficiaryCurrency]              
        ,[Status]
        ,[ProviderId]       
        ,[TransactionRowId]
        ,[UsedExchangeRateId]       
        ,[StatusReason]
        ,[ClientIp]             
        ,[EndProviderId]
        ,[LicenseId]
        ,[TransactionType]
        ,[RetryingTransactionId]
        ,[ExpectedDeliveryOn]
        ,[ExpirationDate]
        ,[MinorityPromoExchangeRate]
        ,[PromoId]
        ,[PromoFeeId]
        ,[PromoFee])
    SELECT
         @transactionId
        ,@senderId
        ,@beneficiaryId
        ,@beneficiaryId     
        ,@minorityExchangeRate
        ,@minorityFee
        ,@totalTransactionAmount
        ,@sentAmount
        ,@beneficiaryReceivableAmount
        ,@senderCurrency
        ,@beneficiaryCurrency
        ,@status
        ,2
        ,NEXT VALUE FOR dbo.Transaction_RowId_Sequence
        ,@usedExchangeRateId
        ,@statusReason
        ,@clientIp      
        ,@endProviderId
        ,@licenseId
        ,@transactionType
        ,@retryingTransactionId
        ,@expectedDeliveryOn
        ,@expirationDate
        ,@minorityPromoExchangeRate
        ,@promoId
        ,@promoFeeId
        ,@promoFee 
        WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Transaction] WHERE SenderId = @senderId AND Created BETWEEN DATEADD(Second, -20,GETUTCDATE()) AND DATEADD(Second, 2,GETUTCDATE()));

        IF (SELECT @@ROWCOUNT) <= 0
            THROW 50001, 'Duplicate Transaction', 1;
COMMIT;
END

Catch the throwed exception and handle it

try
            {
                await _transactionRepository.Insert(transactionInsertEntity);
            }
            catch (Exception ex)
            {
                if (ex.Message == "Duplicate Transaction")
                {
                    throw RemittanceTransactionDuplicateForbiddenException.ForAccount(
                        transaction.BeneficiaryId.GetValueOrDefault(),
                        _remittanceConfiguration.DuplicateTransaction.Seconds);
                }
            }

Test Part

This Test will hit the DB same time so we know the result is it avoiding the duplicate or not and make sure its releasing the lock.

Note: This case not applicable for regression test. If any one of the contacted service response with delay this might affect to hit the db on same time.

var transactionSubmit = V3TransactionController.Submit(
                new TransactionRequest
                {
                    CurrencyCode = setup.CurrencyCode,
                    Beneficiary = requestObject,
                    CountryCode = setup.CountryCode,
                    DestinationAmount = Math.Round(sendAmount * fxRate.MinorityExchangeRate, 2),
                    Fee = fee,
                    FxRateToken = fxRate.ExchangeRateId,
                    TransactionType = TransactionType.Bank,
                    SourceAmount = sendAmount,
                    TotalAmount = sendAmount + fee,
                    PayerId = Guid.Parse("435f0039-9cb7-4db4-a88c-6a6e01906f90")
                });

            var transactionDuplicateSubmit = V3TransactionController.Submit(
                new TransactionRequest
                {
                    CurrencyCode = setup.CurrencyCode,
                    Beneficiary = requestObject,
                    CountryCode = setup.CountryCode,
                    DestinationAmount = Math.Round(sendAmount * fxRate.MinorityExchangeRate, 2),
                    Fee = fee,
                    FxRateToken = fxRate.ExchangeRateId,
                    TransactionType = TransactionType.Bank,
                    SourceAmount = sendAmount,
                    TotalAmount = sendAmount + fee,
                    PayerId = Guid.Parse("435f0039-9cb7-4db4-a88c-6a6e01906f90")
                });

            try
            {
                await Task.WhenAll(transactionSubmit, transactionDuplicateSubmit);
            }
            catch (RemittanceTransactionDuplicateForbiddenException)
            {
                Assert.IsTrue(true);
                var sender = SenderRepository.TryGetBySenderId(State.UserId.GetValueOrDefault());
                sender.Should().NotBeNull();
            }
            catch (Exception)
            {
                Assert.IsTrue(false);
            }

            var transactionCount = await RemittanceSqlRepositoryHelper.GetTransactionCountOfSender(State.UserId.GetValueOrDefault());
            transactionCount.Should().Be(1);